package com.boot.excel.util;

import com.boot.excel.annotation.Excel;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;


public class ExcelUtil<T> {
    /**
     * 实体对象
     */
    public Class<T> clazz;
    private List<T> list;
    private String sheetName;
    private Excel.Type type;
    private List<Field> fields;
    private Workbook wb;
    private int rowAccessWindowSize;
    /**
     * 默认大小
     */
    private int sheetSize = 65536;

    private Sheet sheet;

    public ExcelUtil(Class<T> cls) {
        this(cls,500);
    }
    public ExcelUtil(Class<T> cls,int rowAccessWindowSize) {
        clazz = cls;
        this.rowAccessWindowSize = rowAccessWindowSize;
    }

    /**
     * @param list  导出参数
     * @param sheetName sheetName 名称
     * @return
     */
    public String exportExcel(List<T> list, String sheetName) {
        this.init(list, sheetName, Excel.Type.EXPORT);
        return exportExcel();
    }

    /**
     * 初始化参数
     * @param list
     * @param sheetName
     * @param type
     */
    private void init(List<T> list, String sheetName, Excel.Type type) {
        if (list == null)
        {
            list = new ArrayList<T>();
        }
        this.list = list;
        this.sheetName = sheetName;
        this.type = type;
        createExcelField();
        createWorkbook();
    }


    /**
     * 得到所有定义字段
     */
    private void createExcelField() {
        this.fields = new ArrayList<Field>();
        List<Field> tempFields = new ArrayList<>();
        //获取到父类被注解字段
        tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
        //获取到此类被注解字段
        tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
        for (Field field : tempFields) {
            // 单注解
            if (field.isAnnotationPresent(Excel.class))
            {
                putToField(field, field.getAnnotation(Excel.class));
            }
        }

    }

    /**
     * 放到字段集合中
     * @param field
     * @param attr
     */
    private void putToField(Field field, Excel attr) {
        if(attr != null && (attr.type() == Excel.Type.ALL || attr.type() == type)){
            this.fields.add(field);
        }
    }


    /**
     * 创建一个工作薄
     */
    private void createWorkbook() {
        this.wb = new SXSSFWorkbook(this.rowAccessWindowSize);
    }

    /**
     * 对list数据源将其里面的数据导入到excel表单
     *
     * @return 结果
     */
    public String exportExcel() {
        String name = null;
        OutputStream out = null;
        // 取出一共有多少个sheet.
        double sheetNo = Math.ceil(list.size() / sheetSize);
        for (int index = 0; index <=  sheetNo; index++) {
            createSheet(sheetNo, index);
            Row row = sheet.createRow(0);
            //写入各个字段列头名称
            for (int column = 0; column < fields.size(); column++) {
                Field field = fields.get(column);
                if(field.isAnnotationPresent(Excel.class)){
                    Excel excel = field.getAnnotation(Excel.class);
                    createCell(excel,row,column);
                }
            }
            if (Excel.Type.EXPORT.equals(type))
            {
                fillExcelData(index, row);
            }
        }

        try {
            name = getAbsoluteFile(sheetName);
            out = new FileOutputStream( name);
            wb.write(out);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if (wb != null)
            {
                try
                {
                    wb.close();
                }
                catch (IOException e1)
                {
                    e1.printStackTrace();
                }
            }
            if (out != null)
            {
                try
                {
                    out.close();
                }
                catch (IOException e1)
                {
                    e1.printStackTrace();
                }
            }
        }
        return name;
    }

    /**
     * 填充excel数据
     * @param index
     * @param row
     */
    private void fillExcelData(int index, Row row) {
        int startNo = index * sheetSize;
        int endNo = Math.min(startNo + sheetSize, list.size());

        // 写入各条记录,每条记录对应excel表中的一行
        CellStyle cs = wb.createCellStyle();
        cs.setAlignment(HorizontalAlignment.CENTER);
        cs.setVerticalAlignment(VerticalAlignment.CENTER);
        for (int i = startNo; i < endNo; i++)
        {
            row = sheet.createRow(i + 1 - startNo);
            // 得到导出对象.
            T vo = (T) list.get(i);
            int excelsNo = 0;
            for (int column = 0; column < fields.size(); column++)
            {
                // 获得field.
                Field field = fields.get(column);
                // 设置实体类私有属性可访问
                field.setAccessible(true);
                if (field.isAnnotationPresent(Excel.class))
                {
                    addCell(field.getAnnotation(Excel.class), row, vo, field, column, cs);
                }
            }
        }
    }

    private Cell addCell(Excel attr, Row row, T vo, Field field, int column, CellStyle cs) {
        Cell cell = null;
        try
        {
            // 设置行高
            row.setHeight((short) (attr.height() * 20));
            // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
            if (attr.isExport())
            {
                // 创建cell
                cell = row.createCell(column);
                cell.setCellStyle(cs);

                // 用于读取对象中的属性
                Object value = getTargetValue(vo, field, attr);
                String dateFormat = attr.dateFormat();
                String readConverterExp = attr.readConverterExp();
                if ( value !=null &&StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotEmpty(value.toString()))
                {
                    cell.setCellValue( new SimpleDateFormat(dateFormat).format(value) );
                }
                else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotEmpty(value.toString()))
                {
//                    cell.setCellValue(convertByExp(String.valueOf(value), readConverterExp));
                    cell.setCellValue( String.valueOf(value) );
                }
                else
                {
                    cell.setCellType(CellType.STRING);
                    // 如果数据存在就填入,不存在填入空格.
                    cell.setCellValue("".equals(value) ? attr.defaultValue() : value + attr.suffix());
                }
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        return cell;
    }

    /**
     * 获取bean中的属性值
     *
     * @param vo 实体对象
     * @param field 字段
     * @param excel 注解
     * @return 最终的属性值
     * @throws Exception
     */
    private Object getTargetValue(T vo, Field field, Excel excel) throws Exception
    {
        Object o = field.get(vo);
        if (StringUtils.isNotEmpty(excel.targetAttr()))
        {
            String target = excel.targetAttr();
            if (target.indexOf(".") > -1)
            {
                String[] targets = target.split("[.]");
                for (String name : targets)
                {
                    o = getValue(o, name);
                }
            }
            else
            {
                o = getValue(o, target);
            }
        }
        return o;
    }

    /**
     * 以类的属性的get方法方法形式获取值
     *
     * @param o
     * @param name
     * @return value
     * @throws Exception
     */
    private Object getValue(Object o, String name) throws Exception
    {
        if (StringUtils.isNotEmpty(name))
        {
            Class<?> clazz = o.getClass();
            String methodName = "get" + name.substring(0, 1).toUpperCase() + name.substring(1);
            Method method = clazz.getMethod(methodName);
            o = method.invoke(o);
        }
        return o;
    }


    /**
     * 创建单元格
     * @param excel
     * @param row
     * @param column
     * @return
     */
    private Cell createCell(Excel excel, Row row, int column) {
        Cell cell = row.createCell(column);
        // 设置列中写入内容为String类型
        cell.setCellType(CellType.STRING);
        //写入列名
        cell.setCellValue(excel.name());
        CellStyle cellStyle = createStyle(excel, row, column);
        cell.setCellStyle(cellStyle);
        return cell;
    }


    /**
     * 创建表格样式
     */
    public CellStyle createStyle(Excel attr, Row row, int column)
    {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        if (attr.name().indexOf("注：") >= 0)
        {
            Font font = wb.createFont();
            font.setColor(HSSFFont.COLOR_RED);
            cellStyle.setFont(font);
            cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
            sheet.setColumnWidth(column, 6000);
        }
        else
        {
            Font font = wb.createFont();
            // 粗体显示
            font.setBold(true);
            // 选择需要用到的字体格式
            cellStyle.setFont(font);
            cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());
            // 设置列宽
            sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
            row.setHeight((short) (attr.height() * 20));
        }
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setWrapText(true);
        // 如果设置了提示信息则鼠标放上去提示.
        if (!"".equals(attr.prompt()) )
        {
            // 这里默认设了2-101列提示.
            setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);
        }
        // 如果设置了combo属性则本列只能选择不能输入
        if (attr.combo().length > 0)
        {
            // 这里默认设了2-101列只能选择不能输入.
            setXSSFValidation(sheet, attr.combo(), 1, 100, column, column);
        }
        return cellStyle;
    }

    /**
     * 创建一个sheet
     * @param sheetNo
     * @param index
     */
    private void createSheet(double sheetNo, int index) {
        this.sheet = wb.createSheet();
        // 设置工作表的名称
        if(sheetNo == 0){
            wb.setSheetName(index, sheetName);
        }else {
            wb.setSheetName(index, sheetName + index);
        }
    }


    /**
     * 获取下载路径
     *
     * @param filename 文件名称
     */
    public String getAbsoluteFile(String filename)
    {
        String downloadPath = "D:/profile/" + filename;
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists())
        {
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }
    /**
     * 设置 POI XSSFSheet 单元格提示
     *
     * @param sheet 表单
     * @param promptTitle 提示标题
     * @param promptContent 提示内容
     * @param firstRow 开始行
     * @param endRow 结束行
     * @param firstCol 开始列
     * @param endCol 结束列
     */
    public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
                              int firstCol, int endCol)
    {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        dataValidation.createPromptBox(promptTitle, promptContent);
        dataValidation.setShowPromptBox(true);
        sheet.addValidationData(dataValidation);
    }

    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框.
     *
     * @param sheet 要设置的sheet.
     * @param textlist 下拉框显示的内容
     * @param firstRow 开始行
     * @param endRow 结束行
     * @param firstCol 开始列
     * @param endCol 结束列
     * @return 设置好的sheet.
     */
    public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol)
    {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 加载下拉列表内容
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 数据有效性对象
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        // 处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation)
        {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        }
        else
        {
            dataValidation.setSuppressDropDownArrow(false);
        }

        sheet.addValidationData(dataValidation);
    }
}
